<?php
/**
*
* @package phpBB3
* @version $Id$
* @copyright (c) 2005 phpBB Group
* @license http://opensource.org/licenses/gpl-license.php GNU Public License
*
*/

/**
*/

/**
* @ignore
*/
include_once($phpbb_root_path . 'ofc/php-ofc-library/open-flash-chart.' . $phpEx);
include_once($phpbb_root_path . 'sv_common_fb.' . $phpEx);
include_once($phpbb_root_path . 'sv_common_page.' . $phpEx);

// get time from date format (Ymd). Currently we don't need this function. 
function get_time_from_date($date)
{
	$len = strlen($date);	
	if ($len != 8)
		return 0;
		
	$y = (int)substr($date, 0, 4);
	$m = (int)substr($date, 4, 2);
	$d = (int)substr($date, 6, 2);
	
	if ($y < 1970 || $m > 12 || $d > 31)
		return 0;
		
	$t = ($y - 1970) * 365.242199 * 24 * 60 * 60 + (($m - 1) * 30.4368499 + $d - 1) * 24 * 60 * 60;
	
	return $t;
}
// get date in format (Ymd) from time. 
function get_date_from_time($time)
{
	return date('Ymd', $time);
}

// Adjust timezone between Taiwan and Bluehost hosts for date() function. 
function get_taiwan_time()
{	
	// As I develop in Taiwan, so add this for convenience.
	if (PHP_OS == 'WINNT')
		return time() + 8*60*60;
		 
	// Check if the server is not DST. 
	// According to the United States Naval Observatory, daylight savings time begins the Second Sunday of March and ends on the First Sunday of November.
	// The code peice is from Tower Of Power ( http://www.toosweettobesour.com/2009/03/10/calculating-daylight-savings-time-boundary-in-php/ ).
	$remove_hour = strtotime("Second Sunday March 0");  
	$add_hour = strtotime("First Sunday November 0");  
	$time  = time();  
	if( $time >= $remove_hour && $time < $add_hour )  
	{
		// in DST.
		return time() + 14*60*60;	
	}  
	else  
	{  
		// not in DST.
		return time() + 15*60*60;
	}	
}

// convert the date string in database to the normal format.
// EX: from 20101101 to 2010/11/01
function get_date_string($date)
{
	$len = strlen($date);	
	if ($len != 8)
		return '';
		
	$y = substr($date, 0, 4);
	$m = substr($date, 4, 2);
	$d = substr($date, 6, 2);
	
	return $y . '.' . $m . '.' . $d;
}

// get year from a date (format: YYYYMMDD).
function get_year_from_date($date)
{
	$len = strlen($date);	
	if ($len != 8)
		return '';
		
	$y = substr($date, 0, 4);
	
	return $y;
}

// get month from a date (format: YYYYMMDD).
function get_month_from_date($date)
{
	$len = strlen($date);	
	if ($len != 8)
		return '';
		
	$m = substr($date, 4, 2);
	
	return $m;
}

// get day from a date (format: YYYYMMDD).
function get_day_from_date($date)
{
	$len = strlen($date);	
	if ($len != 8)
		return '';
		
	$d = substr($date, 6, 2);
	
	return $d;
}

// Check if the corp id is valid. 
function is_valid_corp_id($corp_id)
{
	$len = strlen($corp_id);
	
	if ($len < 4 || $len >6)
		return false;
	
	return true;
}

// print service log header.	
function print_service_log($file, $time)
{
	global $print_service_log_start_time;
	 
	$print_service_log_start_time= $time;
	
	echo PHP_EOL . '=========================================' . PHP_EOL;
	echo $file . PHP_EOL;
	echo date('Y/m/d H:i:s', $time) . PHP_EOL . PHP_EOL;
}

// print service log footer.
function print_service_log_end()
{
	global $print_service_log_start_time;

	echo PHP_EOL . 'Run time: ' . (get_taiwan_time() - $print_service_log_start_time) . ' seconds ' . PHP_EOL;
	echo '=========================================' . PHP_EOL . PHP_EOL . PHP_EOL;
}

// This lock class is used to prevent multiple entries of service scripts.
class service_lock {
   private $_lock_str;
   private $_fp;
   
   public function __construct($str ) 
   {
      $this->_lock_str = $str;
      $this->_fp = false;
   }

   public function lock( ) 
   {
	   $fname = $this->_lock_str . '.lock';
		$this->_fp = fopen($fname, "w+");

		if (flock($this->_fp, LOCK_EX | LOCK_NB)) 
		{ 
			// do an exclusive lock
    		return true;
		} 
		else 
		{
    		fclose($this->_fp);
    		$this->_fp = false;
    		return false;
    	}   
	}
   
   // this will be called automatically at the end of scope
   public function __destruct() 
   {
	   if ($this->_fp != false)
	   {
		   flock($this->_fp, LOCK_UN); // release the lock
		   fclose($this->_fp);
	   }
   }
}

function username_exist($name)
{
	global $db;
	
	$sql 	= 'SELECT * FROM phpbb_users WHERE username="' . $name . '"';
	$result = $db->sql_query_limit($sql, 1); 
	if ($db->sql_fetchrow($result))
		return true;
	else
		return false;	
}


// Get the price on the last trading day. 
function get_refered_price($corp_id)
{
	global	$db;
	
	$query		= 'SELECT * FROM sv_stock_price WHERE corp_id="' . $corp_id . '" ORDER BY date DESC'; 
	$result		= $db->sql_query_limit($query, 1);
	if ($row = $db->sql_fetchrow($result))
	{
		return (float)$row['price'];
	}
		
	return 0.0;
}

// get corp. chinese name.
function get_corp_chinese_name($corp_id)
{
	global	$db;
	
	if (is_valid_corp_id($corp_id) == false)
	{
		return '';		
	}
	
	$sql 	= 'SELECT * FROM sv_corp_info WHERE corp_id="' . $corp_id . '"';
	$result = $db->sql_query_limit($sql, 1); 
	if ($row = $db->sql_fetchrow($result))
	{
		$chinese_corp_name = utf8_normalize_nfc($row['chinese_name']);
		return $chinese_corp_name;
	}
	
	return '';
}

function get_username($user_id)
{
	global	$db;
	
	if ($user_id == '')
	{
		return '';		
	}
	
	$sql 	= 'SELECT * FROM ' . USERS_TABLE . ' WHERE user_id=' . $user_id;
	$result = $db->sql_query_limit($sql, 1); 
	if ($row = $db->sql_fetchrow($result))
	{
		return $row['username'];		
	}
	
	return '';
}

// get all corp info from TWSE and OTC. 
function get_all_corp_info($insert_directly = false)
{
	global	$db;
	
	$keyword 		= '股票代碼選擇';
	$keyword_OTC 	= '的支援';

	// insert all corp info.
	$corp_info = ' ';
	$max_cat = 36;

	// get corp list from TWSE.
	for ($i = 1; $i <= $max_cat; ++$i)
	{
		// url is from http://www.twse.com.tw/ch/trading/inc/STKCHOICE/data_Top.htm
		if ($i < 10)
		{
			$url = "http://www.twse.com.tw/ch/trading/inc/STKCHOICE/STK0$i.php";
		}
		else if ($i == $max_cat) // Add for TDR. 
		{
			$url = "http://www.twse.com.tw/ch/trading/inc/STKCHOICE/STK9299.php";
		}
		else
		{
			$url = "http://www.twse.com.tw/ch/trading/inc/STKCHOICE/STK$i.php";
		}
	
		$raw_file = file_get_contents($url);
	
		if ($raw_file == false)
		{
			//echo "error to load $url \n";
			continue;
		}
		
		$content	= strip_tags($raw_file);
		$content	= iconv("BIG-5", "UTF-8//IGNORE", $content);	// convert the web page from BIG-5 to UTF-8.
	
		$begin		= strpos($content, $keyword); 
		if ($begin === 0 )
		{
			echo "cannot find the key word in url:$url\n";
    		continue;
		}
	
		$begin 		+= strlen($keyword);
		$corp_info	.= (' ' . trim(str_replace('&nbsp;', ' ', substr($content, $begin))));
	}
	
	// get corp list from OTC.
	$url = "http://www.otc.org.tw//ch/inc/stksum.php";
		
	$raw_file = file_get_contents($url);
	if ($raw_file == false)
	{
		echo "fail to get url:$url\n";
	}

	$content	= strip_tags($raw_file);
	$content	= iconv("BIG-5", "UTF-8//IGNORE", $content);
		
	$begin		= strpos($content, $keyword_OTC); 
	if ($begin != 0 )
	{
		// keyword found. 
		$begin 		+= strlen($keyword_OTC);		
		$corp_info	.= ("\n\t" . trim(str_replace('&nbsp;', ' ', substr($content, $begin))));
	}
	
	// echo "corp info:$corp_info \n";

	// Connect to DB again. 
	// We may need to re-connect SQL server as getting corp info from TWSE/OTC takes much longer time than expected.
	if (isset($dbpasswd))
	{
		echo "dbpasswd set, re-connect SQL server \n";
		$db->sql_connect($dbhost, $dbuser, $dbpasswd, $dbname, $dbport, false, defined('PHPBB_DB_NEW_LINK') ? PHPBB_DB_NEW_LINK : false);
	}

	$tok = strtok($corp_info, " \t\r\n");
	$i = 0;
	while ($tok !== false) {
    	// echo "Token $i = $tok\n";

	    if ($i % 2 == 0)
    	    $corp_id = $tok;
	    if ($i % 2 == 1)
	    {        
    	    $chinese_name = $tok;
        
    	    if (is_valid_corp_id($corp_id))
    	    {
		        // check if the corp is already there.        
    		    $sql		= 'SELECT * FROM sv_corp_info WHERE corp_id="' . $corp_id . '" '; 
    		    // echo $sql . PHP_EOL;
	        	$result = $db->sql_query_limit($sql, 1);	      
	    	    if ($db->sql_fetchrow($result) == false)
    	    	{
	    	    	if ($insert_directly)
	        		{
		        		// insert to sv_corp_info
	        			$sql = 'INSERT INTO sv_corp_info (corp_id, chinese_name) VALUES ("' . $corp_id . '" ,"' .  $chinese_name . '")';        	
    	    			echo $sql . PHP_EOL;
	    	    		$db->sql_query($sql);
	    	    		
	    	    		// send out some notification. 		        		
		        		$message = "Possible new corp: $corp_id , $chinese_name . Check here: http://tw.stock.yahoo.com/q/q?s=$corp_id";
	    	    		mail( "stockvirtual.info@gmail.com", "get_all_corp_info service", $message, "From: " );
	    	    		echo $message;
        			}
        			else
	        		{
		        		// send out some notification. 		        		
		        		$message = "Possible new corp: $corp_id , $chinese_name . Check here: http://tw.stock.yahoo.com/q/q?s=$corp_id";
	    	    		mail( "stockvirtual.info@gmail.com", "get_all_corp_info service", $message, "From: " );
	    	    		echo $message;
        			}		
        		}
    		}
    	}
        
	    $tok = strtok(" \t\r\n");
    	++$i;
	}
}

// We may need to use this work around as the MySQL is older than 5.1.
function get_correct_utf8_mysql_string($s)
{
    if(empty($s)) return $s;
    $s = preg_match_all("#[\x09\x0A\x0D\x20-\x7E]|
[\xC2-\xDF][\x80-\xBF]|
\xE0[\xA0-\xBF][\x80-\xBF]|
[\xE1-\xEC\xEE\xEF][\x80-\xBF]{2}|
\xED[\x80-\x9F][\x80-\xBF]#x", $s, $m );
    return implode("",$m[0]);
} 

// Check if the user is a newbie. 
function is_newbie($user_id)
{
	if ($user_id == ANONYMOUS)
		return true;
		
	global $db;
	
	$sql	= 'SELECT * FROM sv_transaction WHERE user_id=' . $user_id; 
	$result = $db->sql_query_limit($sql, 1); 		
	if ($db->sql_fetchrow($result))
	{
		return false;
	}
	
	return true;	
}

function is_trading_day($time) 
{
	// Need another checking for trading day , from http://www.twse.com.tw/ch/trading/trading_days.php 
	// ...
		
	// Trading time: Mon~Fri, 9:00~13:30
	if (date(w, $time) == 0 || date(w, $time) == 6)
		return false;  
		
	// Make a build-in non-trading day table.
	$non_trading_day = array(
						'20110404' => 1,
						'20110405' => 1,
						'20110606' => 1,
						'20110912' => 1,
						'20111010' => 1,
						);
	
	$d = date('Ymd', $time);
	
	if ($non_trading_day[$d] == 1)
		return false;
		
	return true;
}
		
function is_trading_time($time) 
{		
	// Trading time: Mon~Fri, 9:00~13:30
	if ( date(G, $time) < 9 || date(G, $time) > 13)
		return false;
	if (date(G, $time) == 13 && date(i, $time) > 30)
		return false;  

	return true;
}

// Get the latest finished trading date in Ymd format. 
function get_latest_trading_date($time) 
{	
	if ( (int)date(G, $time) < 9)
	{
		$time -= 12*60*60;
	}
	
	while (true)
	{
		if (is_trading_day($time))
		{
			break;
		}
		else
		{
			$time -= 24*60*60;
		}
	}

	// return the date in Ymd format, ex: 20101231. This date format is used in SV database.
	return get_date_from_time($time);
}

// Get the start time of the current trading day(today). 
// For simplicity, just use the current time minus 12 hours. 
function get_current_trading_day_start_time($time) 
{
	return $time - 12*60*60;
}

// Get the capital reduction information.
function get_capital_reduction_info()
{
	global $db;
	
	echo "get_capital_reduction_info:\r\n";
	
	$url = "http://www.twse.com.tw/ch/trading/exchange/TWTAVU/TWTAVU.php";
	
	$raw_file = file_get_contents($url);
    if ($raw_file == false)
    	return;

    $content	= iconv("BIG-5", "UTF-8//IGNORE", $raw_file);	// convert the web page from BIG-5 to UTF-8.    
    $begin		= strpos($content, '參考價試算'); 
    $end		= strpos($content, '公式：減資換股率');
    
    if ($begin === 0 || $end === 0)
    	return '';
	
    $begin	= $begin + strlen('參考價試算');
    $str = substr($content, $begin , $end - $begin);
    $str = trim($str);    
    
    // now $str contains several window.open() calls and every entry of capital reduction information has 2 window.open() call. 
    // we will get the url of the 1st call for each entry, then we can get the detail info.
    while(true)
    {
	    $begin 	= strpos($str, "window.open('"); 
	    $end 	= strpos($str, "',null,"); 
	    
	    if ($begin == 0 || $end == 0)
	    {
	    	break;
    	}
	    	
	    $begin	= $begin + strlen("window.open('");
	    
	    $url 	= 'http://www.twse.com.tw/ch/trading/exchange/TWTAVU/' . substr($str, $begin , $end - $begin);
	    //echo "\nurl: $url\n";
	    
	    // get detail info.
	    $raw_file = file_get_contents($url);
    	if ($raw_file)
    	{
    		$content	= strip_tags($raw_file);
    		$content	= iconv("BIG-5", "UTF-8//IGNORE", $content);	// convert the web page from BIG-5 to UTF-8.
    		
    		//echo $content;
    		//echo '=======================\n\n';
    		
    		// corp id
    		$begin 	= strpos($content, '股票代號：'); 
	    	$end 	= strpos($content, '股票名稱：'); 
	    	if ($begin != 0  && $end != 0)
	    	{
		    	$begin += strlen('股票代號：');
	    		$corp_id = substr($content, $begin, $end - $begin);
	    		
	    		echo "corp_id:$corp_id\n";
    		}
    		
    		// scheduled date
    		$begin 	= strpos($content, '停止買賣日期：'); 
	    	$end 	= strpos($content, '每壹仟股換發新股票：'); 
	    	if ($begin != 0  && $end != 0)
	    	{
		    	$begin += strlen('停止買賣日期：');
	    		$scheduled_date = substr($content, $begin, $end - $begin);	    		
    		}
    		
    		// share after change
    		$begin 	= strpos($content, '每壹仟股換發新股票：'); 
	    	$end 	= strpos($content, '股每股退還股款：'); 
	    	if ($begin != 0  && $end != 0)
	    	{
		    	$begin += strlen('每壹仟股換發新股票：');
	    		$share_after_change = substr($content, $begin, $end - $begin);		
    		}
    		
    		// cash after change
    		$begin 	= strpos($content, '股每股退還股款：'); 
	    	$end 	= strpos($content, '元/股減資並(有償)現金增資：'); 
	    	if ($begin != 0  && $end != 0)
	    	{
		    	$begin += strlen('股每股退還股款：');
	    		$cash_after_change = substr($content, $begin, $end - $begin);
    		}
    		
    		// set basic share unit to 1000*1000 for better precision. 
    		$share_unit 		= 1000000;
    		$cash_after_change	= (int)((float)$cash_after_change * 1000);
    		$share_after_change	= (int)((float)$share_after_change * 1000);
    		
    		echo "cash_after_change:$cash_after_change\n";
    		echo "share_after_change:$share_after_change\n";
    		
    		// update the date to 20xx
    		$y = (int)(substr($scheduled_date, 0, 3)) + 1911;
    		$d = $y . substr($scheduled_date, 4, 2) . substr($scheduled_date, 7, 2);
    		$scheduled_date = $d;
    		echo "scheduled_date:$scheduled_date\n";
    		
    		if (is_valid_corp_id($corp_id) && $share_after_change > 0 && $scheduled_date != "")
			{
				$corp_id_after_change = $corp_id;
				$corp_id_after_change_2 = "";
				$share_after_change_2 = 0;
				$corp_id_after_change_3 = "";
				$share_after_change_3 = 0;
		
				$sql = 'SELECT * FROM sv_equity_change_event WHERE corp_id="' . $corp_id . '" AND scheduled_date="' . $scheduled_date . '"';
				$result	= $db->sql_query_limit($sql, 1); 
				if( ! ($db->sql_fetchrow($result)))
				{
					$sql = 'INSERT INTO sv_equity_change_event (corp_id, share_unit, cash_after_change, corp_id_after_change, share_after_change, corp_id_after_change_2, share_after_change_2, corp_id_after_change_3, share_after_change_3, scheduled_date) VALUES ("' . $corp_id . '" ,' . $share_unit . ' , ' . $cash_after_change . ', "' . $corp_id_after_change . '", ' . $share_after_change . ', "' . $corp_id_after_change_2 . '", ' . $share_after_change_2 . ', "' . $corp_id_after_change_3 . '", ' . $share_after_change_3 . ', "' . $scheduled_date . '")';
					echo "$sql\r\n\r\n";
					$result = $db->sql_query($sql);
				}
			}
		}
	    
	    // skip 2nd window.open() call
	    $end2 	= strpos($str, "試算"); 
	    $str = substr($str, $end2 + 4);
    }
}

// Define our own str_getcsv as the PHP on StockVirtual doesn't support this function.
function sv_str_getcsv($input, $delimiter=',', $enclosure='"', $escape=null, $eol=null) 
{
	$temp=fopen("php://memory", "rw");
	fwrite($temp, $input);
	fseek($temp, 0);
	$r = array();
	while (($data = fgetcsv($temp, 84096, $delimiter, $enclosure)) !== false) 
	{
    	$r[] = $data;
	}
	fclose($temp);
	return $r;
}

// get dividend info from OTC.
function get_dividend_from_otc()
{
	global $db;
	
	echo "get_dividend_from_otc - begin\n";
	$raw_file = file_get_contents("http://www.otc.org.tw/en/stock/exright/preAnnounce/Exright_E_DATE.CSV");
    if ($raw_file == false)
    	return false;

    $csv 		= sv_str_getcsv ($raw_file, "\n");
    
    foreach($csv as &$row)
    {
	    $row = sv_str_getcsv($row[0]);
	    if (sizeof($row[0]) != 7)
	    	continue;
		if (is_valid_corp_id($row[0][1]) == false)
			continue;

		$corp_id 			= $row[0][1];
    	$share_unit 		= 1000;
    	$cash_after_change	= (float)$row[0][6] * 1000;
    	$share_after_change	= (float)$row[0][2] * 1000 + 1000;
    	$scheduled_date		= $row[0][0];

    	if ($cash_after_change == 0 &&  $share_after_change == 1000)
    		continue;
    			
    	echo "corp_id: $corp_id\n";
    	echo "cash_after_change:$cash_after_change\n";
    	echo "share_after_change:$share_after_change\n";
    		
    	// update the date to 20xx
    	$y = (int)(substr($scheduled_date, 0, 3)) + 1911;
    	$d = $y . substr($scheduled_date, 3, 2) . substr($scheduled_date, 5, 2);
    	$scheduled_date = $d;
    	echo "scheduled_date:$scheduled_date\n";
    		
    	if (is_valid_corp_id($corp_id) && $share_after_change > 0 && $scheduled_date != "")
		{
			$corp_id_after_change = $corp_id;
			$corp_id_after_change_2 = "";
			$share_after_change_2 = 0;
			$corp_id_after_change_3 = "";
			$share_after_change_3 = 0;
		
			$sql = 'SELECT * FROM sv_equity_change_event WHERE corp_id="' . $corp_id . '" AND scheduled_date="' . $scheduled_date . '"';
			$result	= $db->sql_query_limit($sql, 1); 
			if( ! ($db->sql_fetchrow($result)))
			{
				$sql = 'INSERT INTO sv_equity_change_event (corp_id, share_unit, cash_after_change, corp_id_after_change, share_after_change, corp_id_after_change_2, share_after_change_2, corp_id_after_change_3, share_after_change_3, scheduled_date) VALUES ("' . $corp_id . '" ,' . $share_unit . ' , ' . $cash_after_change . ', "' . $corp_id_after_change . '", ' . $share_after_change . ', "' . $corp_id_after_change_2 . '", ' . $share_after_change_2 . ', "' . $corp_id_after_change_3 . '", ' . $share_after_change_3 . ', "' . $scheduled_date . '")';
				echo "$sql\r\n\r\n";
				$result = $db->sql_query($sql);
				
				$message = 'OTC Corp ' . $corp_id . ' ( ' . get_corp_chinese_name($corp_id) . ' ) ' . ' dividend info: cash_after_change: ' . $cash_after_change . ' share_after_change: ' . $share_after_change . ' scheduled_date: ' . $scheduled_date;
				mail( "stockvirtual.info@gmail.com", "Dividend", $message, "From: " );
			}
		}
	}   
    
	echo "get_dividend_from_otc - end\n";
}

// get dividend info from TWSE.
function get_dividend_from_twse()
{
	global $db;
	
	echo "get_dividend_from_twse - begin\n";
	
	$date = get_date_from_time(get_taiwan_time());
	
	// Prepare the date format for querying to TWSE. 
	$y = get_year_from_date($date);
	$m = get_month_from_date($date);
	$d = get_day_from_date($date);	
	$y = (int)($y) - 1911;
	
	$url = "http://www.twse.com.tw/en/trading/exchange/TWT48U/TWT48U_PD.php?chk_date=$y/$m/$d&Sort_kind=DATE&type=csv";
	$raw_file = file_get_contents($url);
    if ($raw_file == false)
    	return false;

    $csv 		= sv_str_getcsv ($raw_file, "\n");
    
    foreach($csv as &$row)
    {
	    $row = sv_str_getcsv($row[0]);    
	    
	    if (sizeof($row[0]) != 6)
	    {
		    //echo "size not 6\n";
	    	continue;
    	}
		if (is_valid_corp_id($row[0][1]) == false)
		{
			//echo "not valid corp id\n";
			continue;
		}

		$corp_id 			= $row[0][1];
    	$share_unit 		= 1000;
    	$cash_after_change	= (float)$row[0][5]*1000;
    	$share_after_change	= (float)$row[0][2] * 1000 + 1000;
    	$scheduled_date		= $row[0][0];

    	if ($cash_after_change == 0 &&  $share_after_change == 1000)
    		continue;
    			
    	echo "corp_id: $corp_id\n";
    	echo "cash_after_change:$cash_after_change\n";
    	echo "share_after_change:$share_after_change\n";
    		
    	$y = substr($scheduled_date, 0, 4);
    	$d = $y . substr($scheduled_date, 5, 2) . substr($scheduled_date, 8, 2);
    	$scheduled_date = $d;
    	echo "scheduled_date:$scheduled_date\n";
    		
    	if (is_valid_corp_id($corp_id) && $share_after_change > 0 && $scheduled_date != "")
		{
			$corp_id_after_change = $corp_id;
			$corp_id_after_change_2 = "";
			$share_after_change_2 = 0;
			$corp_id_after_change_3 = "";
			$share_after_change_3 = 0;
		
			$sql = 'SELECT * FROM sv_equity_change_event WHERE corp_id="' . $corp_id . '" AND scheduled_date="' . $scheduled_date . '"';
			$result	= $db->sql_query_limit($sql, 1); 
			if( ! ($db->sql_fetchrow($result)))
			{
				$sql = 'INSERT INTO sv_equity_change_event (corp_id, share_unit, cash_after_change, corp_id_after_change, share_after_change, corp_id_after_change_2, share_after_change_2, corp_id_after_change_3, share_after_change_3, scheduled_date) VALUES ("' . $corp_id . '" ,' . $share_unit . ' , ' . $cash_after_change . ', "' . $corp_id_after_change . '", ' . $share_after_change . ', "' . $corp_id_after_change_2 . '", ' . $share_after_change_2 . ', "' . $corp_id_after_change_3 . '", ' . $share_after_change_3 . ', "' . $scheduled_date . '")';
				echo "$sql\r\n\r\n";
				$result = $db->sql_query($sql);
				
				$message = 'TWSE Corp ' . $corp_id . ' ( ' . get_corp_chinese_name($corp_id) . ' ) ' . ' dividend info: cash_after_change: ' . $cash_after_change . ' share_after_change: ' . $share_after_change . ' scheduled_date: ' . $scheduled_date;
				mail( "stockvirtual.info@gmail.com", "Dividend", $message, "From: " );
			}
		}
	}   
    
	echo "get_dividend_from_twse - end\n";
}


// Get the TAIEX index of a specific date. 
function get_taiex($date, $query_twse = false)
{
	global $db;
	
	$len = strlen($date);
	if ($len != 8)
		return '';
		
	// search from database.
	$query	= 'select * from sv_taiex where date="' . $date . '"'; 
	$result	= $db->sql_query_limit($query, 1); 
	if( $row = $db->sql_fetchrow($result))
	{
		return $row['closing_index'];
	}
	
	// If we are not required to query TWSE, return null string as the database don't have the data.
	if ($query_twse == false)
		return '';
		
	// Query from TWSE. 
	$y = get_year_from_date($date);
	$m = get_month_from_date($date);
	$d = get_day_from_date($date);
	
	$y = (int)($y) - 1911;
	$url = "http://www.twse.com.tw/ch/trading/indices/MI_5MINS_HIST/MI_5MINS_HIST.php?myear=$y&mmon=$m";
	//echo $url;
	
    $raw_file = file_get_contents($url);
    if ($raw_file == false)
    	return '';
	
	$content	= strip_tags($raw_file);
    $content	= iconv("BIG-5", "UTF-8//IGNORE", $content);	// convert the web page from BIG-5 to UTF-8.
    $begin		= strpos($content, '發行量加權股價指數歷史資料日期開盤指數最高指數最低指數收盤指數'); 
    $end		= strpos($content, 'function adjustFooterPosition');
    if ($begin === 0 || $end === 0)
    	return '';
	
    $begin	= $begin + strlen('發行量加權股價指數歷史資料日期開盤指數最高指數最低指數收盤指數');
    $str = substr($content, $begin , $end - $begin);
    $str = trim($str);
    
	$tok = strtok($str, " ");
	$i = 0;
	while ($tok !== false) 
	{  	    	
    	if ($i % 5 == 0)		// date
    		$q_date = $tok;
    	if ($i % 5 == 4)		// index
    	{
    		$q_index = $tok;    		
    		
			$y = substr($q_date, 0, strlen($q_date) -6);
			$m = substr($q_date, strlen($q_date) - 5, 2);
			$d = substr($q_date, strlen($q_date) - 2, 2);
    		
    		$y = (int)($y) + 1911;
    		$i_date = '' . $y . $m . $d;
    		
    		$q_index = ereg_replace("[^0-9.]", "", $q_index); 			
    		$i_closing_index = (float)($q_index);
    		
    		// insert the index record into the database.
    		$sql = 'INSERT IGNORE INTO sv_taiex SET date="' . $i_date . '", closing_index=' . $i_closing_index;
    		//echo "    $sql\n";
    		$result	= $db->sql_query($sql); 
    		
    		if ($date == $i_date)
    			return $i_closing_index;
		}
    	
    	$tok = strtok(" ");
    	++$i;
	}
	
    return '';
		
}

// Get the price, volume and related info of a stock.
function get_stock_price_volume($corp_id, &$price, &$volume, &$current_short_term_volume, &$reach_surged_limit, &$reach_decline_limit)
{	
	if (get_stock_price_volume_from_twse($corp_id, $price, $volume, $current_short_term_volume, $reach_surged_limit, $reach_decline_limit))
		return true;
	else
		return get_stock_price_volume_from_yahoo($corp_id, $price, $volume, $current_short_term_volume, $reach_surged_limit, $reach_decline_limit);
}

// Get info from TWSE Market Information System.
function get_stock_price_volume_from_twse($corp_id, &$price, &$volume, &$current_short_term_volume, &$reach_surged_limit, &$reach_decline_limit)
{
	if (is_valid_corp_id($corp_id) == false)
		return false;

	$raw_file = file_get_contents("http://mis.twse.com.tw/data/$corp_id.csv");
    if ($raw_file == false)
    	return false;
	
    $csv 	= sv_str_getcsv ($raw_file);

    $price 		= (float)($csv[0][8]);
    $volume 	= ereg_replace("[^0-9]", "", $csv[0][9]);					// 累積成交數量
    $current_short_term_volume 	= ereg_replace("[^0-9]", "", $csv[0][10]);	// 當盤成交數量
    
    // Check 漲停, 跌停
    $reach_surged_limit = false;
    $reach_decline_limit = false;
    if ($csv[0][3] == $csv[0][8])
    	$reach_surged_limit = true;
    if ($csv[0][4] == $csv[0][8])
    	$reach_decline_limit = true;

    return true;
}

// Get info from Yahoo stock.
function get_stock_price_volume_from_yahoo($corp_id, &$price, &$volume, &$current_short_term_volume, &$reach_surged_limit, &$reach_decline_limit)
{
	if (is_valid_corp_id($corp_id) == false)
		return false;
		
	// get stock price/volume. 
    $raw_file = file_get_contents("http://tw.stock.yahoo.com/q/q?s=$corp_id");
    if ($raw_file == false)
    	return false;
    	
    $content	= strip_tags($raw_file);    
    $content	= iconv("BIG-5", "UTF-8//IGNORE", $content);	// convert the web page from BIG-5 to UTF-8.
    $begin		= strpos($content, '加到投資組合'); 
    $end		= strpos($content, '成交明細技術');
    if ($begin === 0 || $end === 0)
    	return false;
    	
    $begin	= $begin + strlen('加到投資組合');
    $str = substr($content, $begin , $end - $begin);
    $str = trim($str);
	$tok = strtok($str, " ");
	$i = 0;
	while ($tok !== false) 
	{  	    	
    	if ($i == 1)		// index of current price
    		$price = (float)$tok;
    	if ($i == 5)		// index of volume today
    		$volume = $tok;
    	
    	$tok = strtok(" ");
    	++$i;
	}
	
	// leave only numbers. 
	$volume = ereg_replace("[^0-9]", "", $volume); 
	$volume = (int)$volume;
	
	// Yahoo doesn't provide current short term volume, so we just use volume.
	$current_short_term_volume = $volume;
	
	// error checking.
	if ($price < 0.01)
		return false;
	
	return true;
}

function update_all_stock_price()
{
	global $db, $time, $price, $volume;
	
	$latest_trading_date = get_latest_trading_date($time);			
	
	$query	= "select * from sv_corp_info"; 
	$result	= $db->sql_query($query) or die("Failed Query of " . $query); 
	while( $thisrow = $db->sql_fetchrow($result))
	{
    	$corp_id = $thisrow['corp_id'];
    	echo "corp_id: $corp_id\n";
    	
    	// query if the record of (corp, date, price) is already in. 
    	$query2		= 'SELECT * FROM sv_stock_price WHERE corp_id="' . $corp_id . '" AND date=' . $latest_trading_date; 
		$result2	= $db->sql_query_limit($query2, 1);
		if ($db->sql_fetchrow($result2))
		{
			echo "already exist: corp_id:$corp_id date:$latest_trading_date \n";
			continue;
		}
    	    	
    	// Get price, volume 
		$price = $volume = $current_short_term_volume = 0;
		$reach_surged_limit = $reach_decline_limit = false;
		sleep(3);
		if (get_stock_price_volume($corp_id, $price, $volume, $current_short_term_volume, $reach_surged_limit, $reach_decline_limit) == false)
		{			
			echo " fail to get price/volume \n";
			// send out some notification. 
		    $message = "fail to get price/volume: $corp_id, "  . get_corp_chinese_name($corp_id) . '  time: ' . date('Y/m/d H:i:s', $time);
		    $message .= PHP_EOL . 'click here to check: http://tw.stock.yahoo.com/q/q?s=' . $corp_id . ' or http://mis.twse.com.tw/stock_best5.html?stockId=' . $corp_id;
	    	mail( "stockvirtual.info@gmail.com", "update_all_stock_price service", $message, "From: " );
			continue;
		}
		echo "corp_id:$corp_id price:$price volume:$volume \n";
		
		$sql2 = 'INSERT INTO sv_stock_price (corp_id, date, time, price, volume) VALUES ("' . $corp_id . '", ' . "$latest_trading_date, $time, $price, $volume)";
		echo "$sql2 \n";
		$db->sql_query($sql2);
	}
}
	
// deal with all transaction and check if they can be done.
function deal_all_transaction()
{
	global $db, $time, $price, $volume;
	
	$current_trading_day_start_time = get_current_trading_day_start_time(get_taiwan_time()); 
		
	// get TWSE and OTC total value.
//	$total_value_in_twse_otc = get_TWSE_value() + get_OTC_value();
//	echo "Total value in TWSE/OTC: $total_value_in_twse_otc\n";
	
	// get StockVirtual total value today.
//	$query	= 'SELECT SUM(share*price) FROM sv_transaction WHERE deal_time > ' . $current_trading_day_start_time; 		
//	$result	= $db->sql_query($query);
//	$row	= $db->sql_fetchrow($result);
//	$total_value_in_sv = $row['SUM(share*price)'];
//	echo "Total value in StockVirtual: $total_value_in_sv\n";
//	$ratio = $total_value_in_sv * 100 / $total_value_in_twse_otc;
//	echo "ratio: $ratio %\n";
	
	$sql	= "SELECT * FROM sv_transaction WHERE action='to_buy' OR action='to_sell' ORDER BY corp_id"; 
	$result = $db->sql_query($sql); 
		
	$saved_corp_id = '';
		
	while ($thisrow = $db->sql_fetchrow($result))
	{
    	$corp_id 		= $thisrow['corp_id'];
    	$transaction_id	= $thisrow['transaction_id'];    	
    	echo "Checking transaction_id: $transaction_id  corp_id:$corp_id\n";

    	// Delete the delegations older than 5 days.
    	$request_time	= $thisrow['request_time'];
    	if ($request_time + 5 * 24 * 60 * 60 < $current_trading_day_start_time)
    	{
	    	echo "too old delegation, delete and continue to next one\n";
	    	// update the status of the transaction.
	    	$action = $thisrow['action'] . '_deleted';
			$sql2 = 'UPDATE sv_transaction SET action="' . $action . '" WHERE transaction_id=' . $transaction_id;
			$db->sql_query($sql2);
	    	continue;
    	}
    	
    	if (is_valid_corp_id($corp_id) == false)
    	{
	    	echo "not valid corp id, continue to next one\n";
	    	// update the check time/status of the transaction.
			$sql2 = 'UPDATE sv_transaction SET check_time=' . (get_taiwan_time()) . ' WHERE transaction_id=' . $transaction_id;
			$db->sql_query($sql2);
			$sql2 = 'UPDATE sv_transaction SET check_status="corp_id_not_valid" WHERE transaction_id=' . $transaction_id;
			$db->sql_query($sql2);
	    	continue;
    	}
    	
    	// For a different corp, get the latest price/volume info.
    	$price = $volume = $current_short_term_volume = 0;
    	$reach_surged_limit = $reach_decline_limit = false;
    	
    	sleep(3);
		if ($corp_id != $saved_corp_id && get_stock_price_volume($corp_id, $price, $volume, $current_short_term_volume, $reach_surged_limit, $reach_decline_limit) == true)
		{
			$saved_corp_id 						= $corp_id;
			$saved_price 						= $price;
			$saved_volume						= $volume;			
			$saved_current_short_term_volume	= $current_short_term_volume;			
			$saved_reach_surged_limit			= $reach_surged_limit;
			$saved_reach_decline_limit			= $reach_decline_limit;
			echo "saved_corp_id: $saved_corp_id  saved_price:$saved_price saved_volume: $saved_volume current_volume: $current_short_term_volume, surged_limit:$reach_surged_limit, decline limit: $reach_decline_limit\n";
		}

		// Check if the latest corp price/volume info is saved.
		if ($corp_id != $saved_corp_id)
		{
			echo "error: cannot get price/volume info\n";
			// update the check time/status of the transaction.
			$sql2 = 'UPDATE sv_transaction SET check_time=' . (get_taiwan_time()) . ' WHERE transaction_id=' . $transaction_id;
			$db->sql_query($sql2);
			$sql2 = 'UPDATE sv_transaction SET check_status="cannot_get_price" WHERE transaction_id=' . $transaction_id;
			$db->sql_query($sql2);
			continue;
		}		

		$user_id		= $thisrow['user_id'];
		$request_share	= $thisrow['share'];
		$request_price	= $thisrow['price'];
		$action			= $thisrow['action'];
		echo "user_id:$user_id request_share:$request_share request_price:$request_price action:$action \n";
		
		// Check if the dealed volume is big enough in current trading day.
		$query2	= 'SELECT SUM(share) FROM sv_transaction WHERE corp_id="' . $corp_id . '" AND deal_time > ' . $current_trading_day_start_time; 		
		$result2	= $db->sql_query($query2);
		$row2		= $db->sql_fetchrow($result2);
		
		$sum_dealed_share = $row2['SUM(share)'];
		echo "sum_dealed_share: $sum_dealed_share \n";		
		
		// If the stock reaches surged or decline limit, the volume will restrict to current volume. 
		// In this case, most people cannot buy/sell the stocks. 
		if (($action == 'to_buy' && $saved_reach_surged_limit) ||
			($action == 'to_sell' && $saved_reach_decline_limit))
		{	
			// Limit the volume at the lower of 1% of total volume and the current volume.
			$limit = $saved_current_short_term_volume;
			if ($limit > $saved_volume/100)
				$limit = $saved_volume/100;
			if ($limit * 1000 < $request_share + $sum_dealed_share)
			{
				echo "Cannot buy/sell,volume limited on :$limit -  surged/decline limit reached.\n";
			
				// update the check time/status of the transaction.
				$sql2 = 'UPDATE sv_transaction SET check_time=' . (get_taiwan_time()) . ' WHERE transaction_id=' . $transaction_id;
				$db->sql_query($sql2);
				$sql2 = 'UPDATE sv_transaction SET check_status="volume_not_enough" WHERE transaction_id=' . $transaction_id;
				$db->sql_query($sql2);
				continue;	
			}
		}
				
		if ($saved_volume * 1000 < $request_share + $sum_dealed_share)
		{
			// update the check time/status of the transaction.
			$sql2 = 'UPDATE sv_transaction SET check_time=' . (get_taiwan_time()) . ' WHERE transaction_id=' . $transaction_id;
			$db->sql_query($sql2);
			$sql2 = 'UPDATE sv_transaction SET check_status="volume_not_enough" WHERE transaction_id=' . $transaction_id;
			$db->sql_query($sql2);
			continue;
		}
				
		echo "dealed volume is big enough, $saved_volume(1000 shares)\n";
					
		// Delegation is in market price. 	
		if ($request_price == 0.0)
		{
			$dealed_price = $saved_price;
			echo "market price => $dealed_price\n";					
		}
								
		// to buy
		if ($action == 'to_buy')
		{
			$needed_money = $dealed_price * $request_share;
			// add tax.
			$needed_money = $needed_money * 1.001425;
			echo "To buy, needed money: $needed_money \n";
						
			// Check if the user has enough money
			$query2	= "SELECT * FROM phpbb_users WHERE user_id=" . $user_id; 
			$result2	= $db->sql_query_limit($query2, 1);
			$row2		= $db->sql_fetchrow($result2);
			$user_money = $row2['user_money'];
					
			echo "money: $user_money\n";
					
			if ($user_money < $needed_money)
			{
				echo "Not enough money\n";
				// update the check time/status of the transaction.
				$sql2 = 'UPDATE sv_transaction SET check_time=' . (get_taiwan_time()) . ' WHERE transaction_id=' . $transaction_id;
				$db->sql_query($sql2);
				$sql2 = 'UPDATE sv_transaction SET check_status="money_not_enough" WHERE transaction_id=' . $transaction_id;
				$db->sql_query($sql2);
				continue;
			}					
					
			$db->sql_transaction('begin');
						
			// Add the stock to the user
			$query2	= 'SELECT * FROM sv_holding_stock WHERE user_id=' . $user_id . ' AND corp_id="' . $corp_id . '"'; 
			$result2	= $db->sql_query_limit($query2, 1);
			$row2		= $db->sql_fetchrow($result2);						
			if ($row2)
			{
				// the user already has the stock 
				$share 	= (int)$row2['share'] + (int)$request_share;
				$sql 	= 'UPDATE sv_holding_stock SET share=' . $share . ' WHERE user_id=' . $user_id . ' AND corp_id="' . $corp_id . '"';
	       		$db->sql_query($sql);	
	       		
	       		// calculate the average cost
	       		$avg_cost = ($dealed_price * $request_share + $row2['share'] * $row2['cost']) / ($request_share + $row2['share']);
	       		$sql 	= 'UPDATE sv_holding_stock SET cost=' . $avg_cost . ' WHERE user_id=' . $user_id . ' AND corp_id="' . $corp_id . '"';
	       		$db->sql_query($sql);	
			}
			else
			{
				$sql = 'INSERT INTO sv_holding_stock (user_id, corp_id, share, cost) VALUES ("' . $user_id . '", "'. $corp_id . '", "' . $request_share . '", "' . $dealed_price . '")';
           		$db->sql_query($sql);
       		}
						
			// Take the money from the user
			$sql = 'UPDATE phpbb_users SET user_money=user_money-' . $needed_money . ' WHERE user_id=' . $user_id;
			$db->sql_query($sql);
						
			// Update transaction status
			$sql = 'UPDATE sv_transaction SET action="buy" WHERE transaction_id=' . $transaction_id;
			$db->sql_query($sql);
			$sql = 'UPDATE sv_transaction SET deal_time=' . (get_taiwan_time()) . ' WHERE transaction_id=' . $transaction_id;
			$db->sql_query($sql);
			$sql = 'UPDATE sv_transaction SET price=' . $dealed_price . ' WHERE transaction_id=' . $transaction_id;
			$db->sql_query($sql);
						
			$db->sql_transaction('commit');
			echo "buy - done\n";
		}
		// to sell
		else if ($action == 'to_sell')
		{
			// we should double confirm if this delegation is still not deleted by the user.
			$sql2	= "SELECT * FROM sv_transaction WHERE transaction_id=$transaction_id"; 
			$result2 = $db->sql_query($sql2); 
			$row2 = $db->sql_fetchrow($result2);
			if ($row2['action'] != 'to_sell')
			{
				echo "this sell delegation is just deleted by the user.\n";
				continue;
			}			
			
			$money = $dealed_price * $request_share;
			// add tax
			$money = $money * (1 - 0.003 - 0.001425);
			echo "to sell , money: $money\n";
				
			$db->sql_transaction('begin');

			// Remove the stock share from the user
			$query2	= 'SELECT * FROM sv_holding_stock WHERE user_id=' . $user_id . ' AND corp_id="' . $corp_id . '"'; 
			$result2	= $db->sql_query_limit($query2, 1);
			$row2		= $db->sql_fetchrow($result2);						
			if ($row2)
			{
				$share 			= (int)$row2['share'];
				$share_to_sell 	= (int)$row2['share_to_sell'];
				echo "share:$share share_to_sell:$share_to_sell\n";
					
				if ($request_share == $share)
				{
					// remove the corp stock entry
					$sql = 'DELETE FROM sv_holding_stock WHERE user_id=' . $user_id . ' AND corp_id="' . $corp_id . '"';
					$db->sql_query($sql);						
				}
				else if ($share > $request_share)
				{
					$sql 	= 'UPDATE sv_holding_stock SET share=share-' . $request_share . ' WHERE user_id=' . $user_id . ' AND corp_id="' . $corp_id . '"';
	     			$db->sql_query($sql);
     			}
     			
     			// we handle share_to_sell seperately to update it correctly. share_to_sell is a varialbe related to UI onsell page.
     			// Consider this scenario: the user wants to sell corp A and send out a sell delegation. 
     			// The trading time service starts to iterate all transactions and this sell delegation is on the list.
     			// Then the user delete the sell delegation and the action/share_to_sell fields are updated by the UI page.
     			// As this delegation is already on the list of trading time service, it will still be executed.
     			// So we need to handle this case and we should update share and share_to_sell seperately.
     			if ($share_to_sell >= $request_share)
     			{
	      			$sql 	= 'UPDATE sv_holding_stock SET share_to_sell=share_to_sell-' . $request_share . ' WHERE user_id=' . $user_id . ' AND corp_id="' . $corp_id . '"';
	       			$db->sql_query($sql);						
				}
			}					
						
			// Give the money to the user
			$sql = 'UPDATE phpbb_users SET user_money=user_money+' . $money . ' WHERE user_id=' . $user_id;
			$db->sql_query($sql);
						
			// Update transaction status
			$sql = 'UPDATE sv_transaction SET action="sell" WHERE transaction_id=' . $transaction_id;
			$db->sql_query($sql);
			$sql = 'UPDATE sv_transaction SET deal_time=' . (get_taiwan_time()) . ' WHERE transaction_id=' . $transaction_id;
			$db->sql_query($sql);
			$sql = 'UPDATE sv_transaction SET price=' . $dealed_price . ' WHERE transaction_id=' . $transaction_id;
			$db->sql_query($sql);
				
			$db->sql_transaction('commit');
			echo "sell - done\n";
		}			    		

		// After the deal is done, check if the corp name is empty or not. If empty, we may loss this corp and send a notification mail. 
		if (get_corp_chinese_name($corp_id) == '')
		{
			$message = 'Corp ' . $corp_id . ' may be missed.';
			mail( "stockvirtual.info@gmail.com", "{Possible Missed Corp", $message, "From: " );
		}
	} // while ($thisrow = $db->sql_fetchrow($result))	
}

// update all the user values, including the cash money and holding stocks. 
// Also update the performance for 1, 3 and 6 month period.
function update_all_user_value()
{
	global $db, $time;
	
	$sql	= 'SELECT * FROM phpbb_users WHERE user_type=' . USER_NORMAL;
	$result = $db->sql_query($sql);
	
	while ($thisrow = $db->sql_fetchrow($result))
	{		
		$user_id 	= $thisrow['user_id'];
		$money 		= $thisrow['user_money'];
		$value		= $money;
		$date 		= get_latest_trading_date($time);
		echo "user_id:$user_id money:$money\n";
		
		// remember the max portion info.
		$max_corp_id	= 'Cash';			// use cash as default for simplicity.
		$max_value		= $money;
		
		// Check if the user value already exists.
		$sql2 		= "SELECT * FROM sv_user_value WHERE user_id=$user_id AND date=$date";
		echo	"$sql2\n";
		$result2 	= $db->sql_query($sql2);
		if ($db->sql_fetchrow($result2))
		{
			echo "user:$user_id date:$date already exists.\n";
			continue;			
		}		
		
		$sql2		= 'SELECT * FROM sv_holding_stock WHERE user_id=' . $user_id;
		$result2 	= $db->sql_query($sql2);

		// check all stocks. 
		while ($thisrow2 = $db->sql_fetchrow($result2))
		{
			$corp_id 	= $thisrow2['corp_id'];
			$share		= $thisrow2['share'];
			echo "corp_id:$corp_id share:$share\n";
			
			// get the latest corp price.
			$price		= 0;
			$sql3		= 'SELECT * FROM sv_stock_price WHERE corp_id="' . $corp_id . '" ORDER BY date DESC';
			$result3 	= $db->sql_query($sql3); 
			if ($thisrow3 = $db->sql_fetchrow($result3))
			{
				$price = $thisrow3['price'];
				echo "price:$price \n";
			}
			else
			{
				// there is a holding stock but there is no price info. send out notification. 
				$message = 'Corp ' . $corp_id . ' may have problems. The price is not available while we are calculating the user value.';
				mail( "stockvirtual.info@gmail.com", "{Possible Missed Corp - calculating user market value}", $message, "From: " );
			}
			
			$value += $price * $share;	
			
			if ($price * $share > $max_value)
			{
				$max_corp_id	= $corp_id;
				$max_value		= $price * $share;
			}	
		}
		
		echo "total value: $value\n";
		
		// get the previous user value and check if the increase/decrease is abnormal.
		echo 	"get the previous user value and check if the increase/decrease is abnormal\n";
		$sql2 	= "SELECT * FROM sv_user_value WHERE user_id=$user_id ORDER BY date DESC";
		echo	"$sql2\n";
		$result2 	= $db->sql_query_limit($sql2, 1);
		if (($row2 = $db->sql_fetchrow($result2)))
		{
			$previous_value = $row2['value'];
			$r = ($value - $previous_value)/$previous_value;
			
			if ($r > 0.065 || $r < -0.065)
			{
				$message = 'User value of ' .  get_username($user_id) . ' ( '  . $user_id . ' ) may have problems. \r\nThe value change ' . $r . ' is over 6.5%.\r\n Previous value: ' . number_format($previous_value) . ' Now value: ' . number_format($value) . ' Click to check: http://stockvirtual.info/query_user.php?mode=query_user&user_id=' . $user_id . ' Max portion: ' . get_corp_chinese_name($max_corp_id) . '(' . $max_corp_id . ') ' . number_format($max_value);
				echo $message;
				mail( "stockvirtual.info@gmail.com", "{Abnormal User Value}", $message, "From: " );
			}
		}		
		
		// update the user value.
		$sql2 = "INSERT INTO sv_user_value (user_id, value, money, date) VALUES ($user_id, $value, $money, $date)";
		echo "$sql2 \n";
		$db->sql_query($sql2);
		
		// Calculate the ROI performance over x month
		$period = array(1 => 1, 2 => 3, 3 => 6, 4 => 12);
		
		for ($i = 1; $i <= sizeof($period); ++$i)
		{
			// Check if the user has a value record before that time.
			$time_x_month_before = $time - 30 * 24 * 60 * 60 * $period[$i];	
			$date_x_month_before = get_date_from_time($time_x_month_before);
		
			$sql2	 = 'SELECT * FROM sv_user_value  WHERE user_id=' . $user_id . ' AND date < "' . $date_x_month_before . '" ORDER BY date DESC';
			$result2 = $db->sql_query_limit($sql2, 1); 					
			if ($thisrow2 = $db->sql_fetchrow($result2))
			{				
				$old_value = $thisrow2['value'];
				$roi = (float)($value - $old_value)/(float)$old_value;
				echo "user_id:$user_id ROI:$roi period:" . ($period[$i] * 30) . "\n";
				
				// Insert this ROI performance record, or update if already exists.
				$sql2	 = 'SELECT * FROM sv_user_roi  WHERE user_id=' . $user_id . ' AND period=' . ($period[$i] * 30);
				$result2 = $db->sql_query_limit($sql2, 1); 
				if ($thisrow2 = $db->sql_fetchrow($result2))
				{					
					$sql2 	= 'UPDATE sv_user_roi SET roi=' . $roi . ' WHERE user_id=' . $user_id . ' AND period=' . ($period[$i] * 30);
					echo "$sql2 \n";
					$result2 = $db->sql_query($sql2); 
				}
				else
				{
					$sql2 = "INSERT INTO sv_user_roi (user_id, roi, period) VALUES ($user_id, $roi, " . ($period[$i] * 30) . ")";
					echo "$sql2 \n";
					$result2 = $db->sql_query($sql2); 
				}				
			}
		}
	}
}

// delete/cancel the delegation
function delete_delegation($trans_id)
{
	global $db;	
	
	$sql		= 'SELECT * FROM sv_transaction WHERE transaction_id=' . $trans_id;
	$result 	= $db->sql_query_limit($sql, 1); 
	if ($thisrow = $db->sql_fetchrow($result))
	{
		$action = $thisrow['action'];
		
		// restore the to sell number of the holding stock.
		if ($action == 'to_sell')
		{
			$user_id 	= $thisrow['user_id'];
			$corp_id 	= $thisrow['corp_id'];
			$share		= $thisrow['share'];			

			$sql		= 'SELECT * FROM sv_holding_stock WHERE user_id=' . $user_id . ' AND corp_id="' . $corp_id . '"';
			$result 	= $db->sql_query_limit($sql, 1);
			if ($thisrow = $db->sql_fetchrow($result))
			{
				if ((int)$thisrow['share_to_sell'] < $share)
				{
					// something wrong.
					return false;
				}
				
				$share_to_sell = (int)$thisrow['share_to_sell'] - $share;
	
				$db->sql_transaction('begin');	
	
				$sql 	= 'UPDATE sv_holding_stock SET share_to_sell =' . $share_to_sell . ' WHERE user_id=' . $user_id . ' AND corp_id="' . $corp_id . '"';
				$db->sql_query($sql);
				
				$action = $action . '_deleted';
				$sql 	= 'UPDATE sv_transaction SET action="' . $action . '" WHERE transaction_id=' . $trans_id;
				$db->sql_query($sql);	
			
				$db->sql_transaction('commit');
			}
		}
		else
		{			
			$action = $action . '_deleted';
			$sql 	= 'UPDATE sv_transaction SET action="' . $action . '" WHERE transaction_id=' . $trans_id;
			$db->sql_query($sql);
		}
		
		return true;
	}
	
	return false;
}


// Handle all equity change events, including dispatch stock/cash dividend and decreasing equity.
function handle_equity_change_event()
{
	global $db, $time;
	
	$date = get_date_from_time($time);
	
	$sql	= 'SELECT * FROM sv_equity_change_event WHERE status="new"';
	$result = $db->sql_query($sql);	
	while ($row = $db->sql_fetchrow($result))
	{
		$scheduled_date			= $row['scheduled_date'];
		// If today is not the scheduled day, skip it.
		if ($date != $scheduled_date)
		{
			continue;
		}
		
		$equity_change_event_id = $row['equity_change_event_id'];
		$corp_id				= $row['corp_id'];
		$share_unit				= $row['share_unit'];
		$cash_after_change		= $row['cash_after_change'];
		$corp_id_after_change	= $row['corp_id_after_change'];
		$share_after_change		= $row['share_after_change'];
		$corp_id_after_change_2	= $row['corp_id_after_change_2'];
		$share_after_change_2	= $row['share_after_change_2'];
		$corp_id_after_change_3	= $row['corp_id_after_change_3'];
		$share_after_change_3	= $row['share_after_change_3'];
	
		echo "handle_equity_change_event, id: $equity_change_event_id, corp_id:$corp_id cash:$cash_after_change share:$share_after_change\n";
		if (is_valid_corp_id($corp_id) == false)
		{
			continue;
		}
		
		$sql2	= 'SELECT * FROM sv_holding_stock WHERE corp_id="' . $corp_id . '"';
		$result2 = $db->sql_query($sql2);	
		while ($row2 = $db->sql_fetchrow($result2))
		{
			$holding_stock_id	= $row2['holding_stock_id'];			
			$user_id			= $row2['user_id'];
			$share				= $row2['share'];
			$cost				= $row2['cost'];
			
			echo "user:$user_id share:$share\n";
			
			// Calculate the share/cash which the user should get.
			$user_get_cash_after_change			= (int)($share * $cash_after_change / $share_unit);
			
			$user_get_corp_id_after_change		= $corp_id_after_change;
			$user_get_corp_id_after_change_2	= $corp_id_after_change_2;
			$user_get_corp_id_after_change_3	= $corp_id_after_change_3;
			
			$user_get_share_after_change		= (int)($share * $share_after_change / $share_unit);
			$user_get_share_after_change_2		= (int)($share * $share_after_change_2 / $share_unit);
			$user_get_share_after_change_3		= (int)($share * $share_after_change_3 / $share_unit);
			
			// Update the cost info, based on the assumption that no other corp shares are dispatched. 			
			$new_cost = $share * $cost / $user_get_share_after_change;
			
			$db->sql_transaction('begin');
			
			// Update user holding stock and cash.
			if ($corp_id == $user_get_corp_id_after_change)
			{
				// We will have same corp in the most cases. 
				$sql3 = 'UPDATE sv_holding_stock SET corp_id="' . $user_get_corp_id_after_change . '" WHERE holding_stock_id=' . $holding_stock_id;
				echo "$sql3 \n";
				$db->sql_query($sql3);
				$sql3 = 'UPDATE sv_holding_stock SET share=' . $user_get_share_after_change . ' WHERE holding_stock_id=' . $holding_stock_id;
				echo "$sql3 \n";
				$db->sql_query($sql3);
				$sql3 = 'UPDATE sv_holding_stock SET share_to_sell=0 WHERE holding_stock_id=' . $holding_stock_id;
				echo "$sql3 \n";
				$db->sql_query($sql3);
				$sql3 = 'UPDATE sv_holding_stock SET cost=' . $new_cost . ' WHERE holding_stock_id=' . $holding_stock_id;
				echo "$sql3 \n";
				$db->sql_query($sql3);
			}
			else
			{
				// Seldomly, the 1st corp id may change. 
				$sql3 = 'INSERT INTO sv_holding_stock (user_id, corp_id, share, cost) VALUES ("' . $user_id . '", "'. $user_get_corp_id_after_change . '", "' . $user_get_share_after_change . '", '. $new_cost . ' )';
				echo "$sql3 \n";
				$db->sql_query($sql3);

				$sql3 = 'DELETE FROM sv_holding_stock WHERE holding_stock_id=' . $holding_stock_id;
				echo "$sql3 \n";
				$db->sql_query($sql3);
			}
			
			if ($user_get_cash_after_change > 0)
			{
				$sql3 = 'UPDATE phpbb_users SET user_money=user_money+' . $user_get_cash_after_change . ' WHERE user_id=' . $user_id;
				echo "$sql3 \n";
				$db->sql_query($sql3);
			}
			if ($user_get_share_after_change_2 > 0)
			{
				$sql3 = 'INSERT INTO sv_holding_stock (user_id, corp_id, share, cost) VALUES ("' . $user_id . '", "'. $user_get_corp_id_after_change_2 . '", "' . $user_get_share_after_change_2 . '", "0")';
				echo "$sql3 \n";
				$db->sql_query($sql3);
				
			}
			if ($user_get_share_after_change_3 > 0)
			{
				$sql3 = 'INSERT INTO sv_holding_stock (user_id, corp_id, share, cost) VALUES ("' . $user_id . '", "'. $user_get_corp_id_after_change_3 . '", "' . $user_get_share_after_change_3 . '", "0")';
				echo "$sql3 \n";
				$db->sql_query($sql3);
			}
			
			// Add equity change log.
			$sql3 = 'INSERT INTO sv_equity_change_log (equity_change_event_id, execution_time, user_id, corp_id, share, cash_after_change, corp_id_after_change, share_after_change, corp_id_after_change_2, share_after_change_2, corp_id_after_change_3, share_after_change_3) VALUES ';
			$sql3 .= ' (' . $equity_change_event_id . ', ' . $time . ', ' . $user_id . ', "' . $corp_id . '", ' . $share . ', ' . $user_get_cash_after_change . ', ';
			$sql3 .= ' "'. $user_get_corp_id_after_change . '",' . $user_get_share_after_change . ', "' .  $user_get_corp_id_after_change_2 . '",' . $user_get_share_after_change_2 . ', "' .  $user_get_corp_id_after_change_3 . '",' . $user_get_share_after_change_3 . ')';
			echo "$sql3 \n";
			$db->sql_query($sql3);
			
			$db->sql_transaction('commit');
			
		}
		
		// Update the event status and execution date. 
		$sql2 = 'UPDATE sv_equity_change_event SET status="executed" WHERE equity_change_event_id=' . $equity_change_event_id;
		echo "$sql2 \n";
		$db->sql_query($sql2);
		$sql2 = 'UPDATE sv_equity_change_event SET execution_date="' . get_date_from_time($time) . '" WHERE equity_change_event_id=' . $equity_change_event_id;
		echo "$sql2 \n";
		$db->sql_query($sql2);
	}
}

// Get the TWSE value (price*volume) from Market Information System.
function get_TWSE_value()
{
    $raw_file = file_get_contents("http://mis.twse.com.tw/data/TSEIndex.csv");
    if ($raw_file == false)
    	return false;

    $csv 	= sv_str_getcsv ($raw_file);
    $value 	= ereg_replace("[^0-9]", "", $csv[0][164]);
	
	return $value;
}

// Get the OTC value (price*volume) from Market Information System.
function get_OTC_value()
{
    $raw_file = file_get_contents("http://mis.twse.com.tw/data/OTCIndex.csv");
    if ($raw_file == false)
    	return false;

    $csv 	= sv_str_getcsv ($raw_file);
    $value 	= ereg_replace("[^0-9]", "", $csv[0][98]);    
	
	return $value;
}

// Get the table prefix of knowledge tables.
function get_prefix_of_knowledge_table()
{
	return 'drupal7_';	
}

// Check if the knowledge databses exist or not.
function is_knowledge_db_ok()
{
	global $db;
	
	$table_name = get_prefix_of_knowledge_table() . 'node';
	
	$sql 	= 'SHOW TABLES LIKE "' . $table_name . '"';
	$result = $db->sql_query($sql); 
	if ($row = $db->sql_fetchrow($result))
	{
		return true;
	}
	
	return false;
}

// Get the titles of knowledge articles.
function get_knowledge_titles()
{
	global $db;
	
	$table_name = get_prefix_of_knowledge_table() . 'node';
	$titles		= array();
	
	$sql 	= 'SELECT * FROM ' . $table_name;
	$result = $db->sql_query($sql); 
	while (($row = $db->sql_fetchrow($result)))
	{
		$id 	= $row['vid'];
		$title	= $row['title'];
		
		$titles[$id]	= $title;		
	}
	
	return $titles;
}

// Detect a URL in a String and Convert it to a Clickable Link
function autolink($string)
{
$content_array = explode(" ", $string);
$output = '';

foreach($content_array as $content)
{
//starts with http://
if(substr($content, 0, 7) == "http://")
$content = '<a href="' . $content . '" target="_blank">' . $content . '</a>';

//starts with www.
if(substr($content, 0, 4) == "www.")
$content = '<a href="http://' . $content . '" target="_blank">' . $content . '</a>';

$output .= " " . $content;
}

$output = trim($output);
return $output;
}

// Check if the user is following anyone.
function is_following_anyone($user_id)
{
	global $db;
	
	$sql = 'SELECT * FROM sv_follow_list WHERE follower_user_id=' . $user_id;
	$result = $db->sql_query_limit($sql, 1); 
	if ($row = $db->sql_fetchrow($result))
	{
		return true;
	}
	else
	{
		return false;
	}
}

// Calculate and record the number of visiting daily, weekly and monthly users 
function cal_visiting_user_num()
{
	global $db;
	
	$now		= get_taiwan_time();
	$day_ago	= $now - 1 * 24 * 60 * 60;
	$week_ago	= $now - 7 * 24 * 60 * 60;
	$month_ago	= $now - 30 * 24 * 60 * 60;
	
	$sql 	= 'SELECT COUNT(user_id) as num FROM phpbb_users WHERE user_lastvisit > ' . $day_ago;
	$result = $db->sql_query($sql); 
	$row = $db->sql_fetchrow($result);
	$daily_user_num = $row['num'];	
	
	$sql 	= 'SELECT COUNT(user_id) as num FROM phpbb_users WHERE user_lastvisit > ' . $week_ago;
	$result = $db->sql_query($sql); 
	$row = $db->sql_fetchrow($result);
	$weekly_user_num = $row['num'];	
	
	$sql 	= 'SELECT COUNT(user_id) as num FROM phpbb_users WHERE user_lastvisit > ' . $month_ago;
	$result = $db->sql_query($sql); 
	$row = $db->sql_fetchrow($result);
	$monthly_user_num = $row['num'];	
	
	$date	= get_date_from_time($now);
	
	// insert the record into the database.
    $sql = 'INSERT IGNORE INTO sv_visiting_user SET date="' . $date . '", daily_user_num=' . $daily_user_num . ', weekly_user_num=' . $weekly_user_num . ', monthly_user_num=' . $monthly_user_num;
    $result	= $db->sql_query($sql); 
}

?>